using Dapper;
using Lzfy_His_Service.Models;
using Lzfy_His_Service.Services;
using System.Data;
using System.Text;

namespace Lzfy_His_Service.Repositories
{
    /// <summary>
    /// 线上退费记录仓储实现
    /// </summary>
    public class PrepayRefundDetailRepository : IPrepayRefundDetailRepository
    {
        private readonly IOracleConnectionFactory _oracleConnectionFactory;
        private readonly ILogger<PrepayRefundDetailRepository> _logger;

        public PrepayRefundDetailRepository(
            IOracleConnectionFactory oracleConnectionFactory,
            ILogger<PrepayRefundDetailRepository> logger)
        {
            _oracleConnectionFactory = oracleConnectionFactory;
            _logger = logger;
        }

        /// <summary>
        /// 获取线上退费记录列表（分页）
        /// </summary>
        public async Task<(List<PrepayRefundDetail> records, int totalCount)> GetPrepayRefundDetailListAsync(
            string? patientId = null,
            string? patName = null,
            string? balanceSn = null,
            DateTime? startDate = null,
            DateTime? endDate = null,
            int pageIndex = 1,
            int pageSize = 20)
        {
            using var connection = _oracleConnectionFactory.CreateConnection();
            
            var whereConditions = new List<string>();
            var parameters = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(patientId))
            {
                whereConditions.Add("PATIENT_ID = :PatientId");
                parameters.Add(":PatientId", patientId);
            }

            if (!string.IsNullOrWhiteSpace(patName))
            {
                whereConditions.Add("PAT_NAME LIKE :PatName");
                parameters.Add(":PatName", $"%{patName}%");
            }

            if (!string.IsNullOrWhiteSpace(balanceSn))
            {
                whereConditions.Add("BALANCE_SN = :BalanceSn");
                parameters.Add(":BalanceSn", balanceSn);
            }

            if (startDate.HasValue)
            {
                whereConditions.Add("INPUT_DATE >= :StartDate");
                parameters.Add(":StartDate", startDate.Value);
            }

            if (endDate.HasValue)
            {
                whereConditions.Add("INPUT_DATE <= :EndDate");
                parameters.Add(":EndDate", endDate.Value.AddDays(1).AddSeconds(-1));
            }

            var whereClause = whereConditions.Count > 0 ? $"WHERE {string.Join(" AND ", whereConditions)}" : "";

            // 查询总数
            var countSql = $@"
                SELECT COUNT(1) 
                FROM PCA.PCA_PREPAY_REFUND_DETAIL 
                {whereClause}";

            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);

            // 查询数据（使用Oracle的ROWNUM分页）
            var offset = (pageIndex - 1) * pageSize;
            var pageSql = $@"
                SELECT * FROM (
                    SELECT a.*, ROWNUM rn FROM (
                        SELECT 
                            BALANCE_SN as BalanceSn,
                            INPUT_DATE as InputDate,
                            REFUND_BALANCE as RefundBalance,
                            PATIENT_ID as PatientId,
                            PAT_NAME as PatientName,
                            REFUND_TYPE_NAME as RefundTypeName,
                            AUDIT_DATE as AuditTime,
                            ACCT_DATE as SettleTime,
                            AUDIT_USER_ID as AuditUserId,
                            AUDIT_USER_NAME as AuditUserName,
                            BANK_CARD_HOLDER as BankCardHolder,
                            ACCT_ID as AcctId,
                            REFUND_BANK_CITY as RefundBankCity,
                            PUBLIC_AGENT_REFUND_BANK_CITY as PublicAgentRefundBankCity
                        FROM PCA.PCA_PREPAY_REFUND_DETAIL
                        {whereClause}
                        ORDER BY INPUT_DATE DESC
                    ) a WHERE ROWNUM <= :EndRow
                ) WHERE rn > :StartRow";

            parameters.Add(":StartRow", offset);
            parameters.Add(":EndRow", offset + pageSize);

            var records = await connection.QueryAsync<PrepayRefundDetail>(pageSql, parameters);

            return (records.ToList(), totalCount);
        }

        /// <summary>
        /// 根据退费流水号获取线上退费记录
        /// </summary>
        public async Task<PrepayRefundDetail?> GetPrepayRefundDetailByBalanceSnAsync(string balanceSn)
        {
            using var connection = _oracleConnectionFactory.CreateConnection();
            
            var sql = @"
                SELECT 
                    BALANCE_SN as BalanceSn,
                    INPUT_DATE as InputDate,
                    REFUND_BALANCE as RefundBalance,
                    PATIENT_ID as PatientId,
                    PAT_NAME as PatientName,
                    REFUND_TYPE_NAME as RefundTypeName,
                    AUDIT_DATE as AuditTime,
                    ACCT_DATE as SettleTime,
                    AUDIT_USER_ID as AuditUserId,
                    AUDIT_USER_NAME as AuditUserName,
                    BANK_CARD_HOLDER as BankCardHolder,
                    ACCT_ID as AcctId,
                    REFUND_BANK_CITY as RefundBankCity,
                    PUBLIC_AGENT_REFUND_BANK_CITY as PublicAgentRefundBankCity
                FROM PCA.PCA_PREPAY_REFUND_DETAIL 
                WHERE BALANCE_SN = :BalanceSn";

            return await connection.QuerySingleOrDefaultAsync<PrepayRefundDetail>(sql, new { BalanceSn = balanceSn });
        }

        /// <summary>
        /// 根据患者ID获取线上退费记录列表
        /// </summary>
        public async Task<List<PrepayRefundDetail>> GetPrepayRefundDetailsByPatientIdAsync(string patientId)
        {
            using var connection = _oracleConnectionFactory.CreateConnection();
            
            var sql = @"
                SELECT 
                    BALANCE_SN as BalanceSn,
                    INPUT_DATE as InputDate,
                    REFUND_BALANCE as RefundBalance,
                    PATIENT_ID as PatientId,
                    PAT_NAME as PatientName,
                    REFUND_TYPE_NAME as RefundTypeName,
                    AUDIT_DATE as AuditTime,
                    ACCT_DATE as SettleTime,
                    AUDIT_USER_ID as AuditUserId,
                    AUDIT_USER_NAME as AuditUserName,
                    BANK_CARD_HOLDER as BankCardHolder,
                    ACCT_ID as AcctId,
                    REFUND_BANK_CITY as RefundBankCity,
                    PUBLIC_AGENT_REFUND_BANK_CITY as PublicAgentRefundBankCity
                FROM PCA.PCA_PREPAY_REFUND_DETAIL 
                WHERE PATIENT_ID = :PatientId
                ORDER BY INPUT_DATE DESC";

            var records = await connection.QueryAsync<PrepayRefundDetail>(sql, new { PatientId = patientId });
            return records.ToList();
        }

        /// <summary>
        /// 创建线上退费记录
        /// </summary>
        public async Task<bool> CreatePrepayRefundDetailAsync(PrepayRefundDetail prepayRefundDetail)
        {
            using var connection = _oracleConnectionFactory.CreateConnection();
            
            var sql = @"
                INSERT INTO PCA.PCA_PREPAY_REFUND_DETAIL (
                    BALANCE_SN, 
                    INPUT_DATE, 
                    REFUND_BALANCE, 
                    PATIENT_ID, 
                    PAT_NAME, 
                    REFUND_TYPE_NAME, 
                    AUDIT_DATE,
                    ACCT_DATE,
                    AUDIT_USER_ID,
                    AUDIT_USER_NAME,
                    BANK_CARD_HOLDER, 
                    ACCT_ID,
                    REFUND_BANK_CITY,
                    PUBLIC_AGENT_REFUND_BANK_CITY
                ) VALUES (
                    :BalanceSn, 
                    :InputDate, 
                    :RefundBalance, 
                    :PatientId, 
                    :PatientName, 
                    :RefundTypeName, 
                    :AuditTime,
                    :SettleTime,
                    :AuditUserId,
                    :AuditUserName,
                    :BankCardHolder, 
                    :AcctId,
                    :RefundBankCity,
                    :PublicAgentRefundBankCity
                )";

            var parameters = new
            {
                prepayRefundDetail.BalanceSn,
                InputDate = prepayRefundDetail.InputDate ?? DateTime.Now,
                prepayRefundDetail.RefundBalance,
                prepayRefundDetail.PatientId,
                prepayRefundDetail.PatientName,
                prepayRefundDetail.RefundTypeName,
                prepayRefundDetail.AuditTime,
                prepayRefundDetail.SettleTime,
                prepayRefundDetail.AuditUserId,
                prepayRefundDetail.AuditUserName,
                prepayRefundDetail.BankCardHolder,
                prepayRefundDetail.AcctId,
                prepayRefundDetail.RefundBankCity,
                prepayRefundDetail.PublicAgentRefundBankCity
            };

            var result = await connection.ExecuteAsync(sql, parameters);
            return result > 0;
        }

        /// <summary>
        /// 更新线上退费记录
        /// </summary>
        public async Task<bool> UpdatePrepayRefundDetailAsync(PrepayRefundDetail prepayRefundDetail)
        {
            using var connection = _oracleConnectionFactory.CreateConnection();
            
            var sql = @"
                UPDATE PCA.PCA_PREPAY_REFUND_DETAIL 
                SET 
                    REFUND_BALANCE = :RefundBalance, 
                    REFUND_TYPE_NAME = :RefundTypeName, 
                    BANK_CARD_HOLDER = :BankCardHolder, 
                    ACCT_ID = :AcctId,
                    REFUND_BANK_CITY = :RefundBankCity,
                    PUBLIC_AGENT_REFUND_BANK_CITY = :PublicAgentRefundBankCity
                WHERE BALANCE_SN = :BalanceSn";

            var parameters = new
            {
                prepayRefundDetail.BalanceSn,
                prepayRefundDetail.RefundBalance,
                prepayRefundDetail.RefundTypeName,
                prepayRefundDetail.BankCardHolder,
                prepayRefundDetail.AcctId,
                prepayRefundDetail.RefundBankCity,
                prepayRefundDetail.PublicAgentRefundBankCity
            };

            var result = await connection.ExecuteAsync(sql, parameters);
            return result > 0;
        }

        /// <summary>
        /// 删除线上退费记录
        /// </summary>
        public async Task<bool> DeletePrepayRefundDetailAsync(string balanceSn)
        {
            using var connection = _oracleConnectionFactory.CreateConnection();
            
            var sql = "DELETE FROM PCA.PCA_PREPAY_REFUND_DETAIL WHERE BALANCE_SN = :BalanceSn";
            var result = await connection.ExecuteAsync(sql, new { BalanceSn = balanceSn });
            return result > 0;
        }

        /// <summary>
        /// 审核线上退费记录
        /// </summary>
        public async Task<bool> AuditPrepayRefundDetailAsync(string balanceSn, string auditUserId, string auditUserName)
        {
            using var connection = _oracleConnectionFactory.CreateConnection();
            
            var sql = @"
                UPDATE PCA.PCA_PREPAY_REFUND_DETAIL 
                SET 
                    AUDIT_DATE = :AuditDate, 
                    AUDIT_USER_ID = :AuditUserId, 
                    AUDIT_USER_NAME = :AuditUserName
                WHERE BALANCE_SN = :BalanceSn";

            var parameters = new
            {
                AuditDate = DateTime.Now,
                AuditUserId = auditUserId,
                AuditUserName = auditUserName,
                BalanceSn = balanceSn
            };

            var result = await connection.ExecuteAsync(sql, parameters);
            return result > 0;
        }

        /// <summary>
        /// 结算线上退费记录
        /// </summary>
        public async Task<bool> SettlePrepayRefundDetailAsync(string balanceSn)
        {
            using var connection = _oracleConnectionFactory.CreateConnection();
            
            var sql = "UPDATE PCA.PCA_PREPAY_REFUND_DETAIL SET ACCT_DATE = :SettleTime WHERE BALANCE_SN = :BalanceSn";
            var result = await connection.ExecuteAsync(sql, new { SettleTime = DateTime.Now, BalanceSn = balanceSn });
            return result > 0;
        }

        /// <summary>
        /// 检查退费流水号是否存在
        /// </summary>
        public async Task<bool> IsBalanceSnExistsAsync(string balanceSn)
        {
            using var connection = _oracleConnectionFactory.CreateConnection();
            
            var sql = "SELECT COUNT(1) FROM PCA.PCA_PREPAY_REFUND_DETAIL WHERE BALANCE_SN = :BalanceSn";
            var count = await connection.QuerySingleAsync<int>(sql, new { BalanceSn = balanceSn });
            return count > 0;
        }
    }
}